﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace Dal
{
    public class FTPDao
    {

        public static DataSet getDistributorsFTPProgress(string ambient, string minRows)
        {
            string connection = Credentials.getCredentias(ambient); 
            if (connection == null)
            {
                return null;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Report_csIntegrationXDist";
            cmd.Parameters.AddWithValue("@reg", minRows);
            cmd.CommandTimeout = 0;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtDistributorsFTPProgress");

            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getDistributorsFTPProgress: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsDistributorsFTPProgress");
            ds.Tables.Add(dt);
            return ds;
        }

        public static DataSet getTriStateFTPReport(string ambient, DateTime startDate, DateTime endDate)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return null;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Report_csFTPtristateReport";
            cmd.Parameters.AddWithValue("@date1", startDate);
            cmd.Parameters.AddWithValue("@date2", endDate);
            cmd.CommandTimeout = 0;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtTristateReport");

            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getTriStateFTPReport: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsTristateReport");
            ds.Tables.Add(dt);
            return ds;
        }

        public static DataSet getTriStateFTPReportDetailed(string ambient, int idDistributor, DateTime startDate, DateTime endDate)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return null;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "dbo.dms_Report_csFTPtristateReport_Detailed";
            cmd.Parameters.AddWithValue("@idDistributor", idDistributor);
            cmd.Parameters.AddWithValue("@date1", startDate);
            cmd.Parameters.AddWithValue("@date2", endDate);
            cmd.CommandTimeout = 0;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtTristateReportDetailed");

            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getTriStateFTPReport_Detailed: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsTristateReportDetailed");
            ds.Tables.Add(dt);
            return ds;
        }

        public static DataSet getDistributorsFTPActivity(string ambient, DateTime startDate, DateTime endDate, bool date)
        {
            string connection = Credentials.getCredentias(ambient);
            if (connection == null)
            {
                return null;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = new SqlConnection(connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            if (date)
            {
                cmd.CommandText = "dbo.dms_Report_csFtpLoadSuccessFailed_Date";
            }
            else
            {
                cmd.CommandText = "dbo.dms_Report_csFtpLoadSuccessFailed";
            }
            cmd.Parameters.AddWithValue("@date1", startDate);
            cmd.Parameters.AddWithValue("@date2", endDate);
            cmd.CommandTimeout = 0;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("dtDistributorsActivity");

            try
            {
                cmd.Connection.Open();
                da.Fill(dt);
                cmd.Connection.Close();
            }
            catch (Exception ex)
            {
                Credentials.writeLogFile("getDistributorsFTPActivity: " + ex.Message);
                return null;
            }
            DataSet ds = new DataSet("dsDistributorsActivity");
            ds.Tables.Add(dt);
            return ds;
        }

       public static DataSet getDistributorsFTPStatus(string ambient)
       {
           string connection = Credentials.getCredentias(ambient);
           if (connection == null)
           {
               return null;
           }
           SqlCommand cmd = new SqlCommand();
           cmd.Connection = new SqlConnection(connection);
           cmd.CommandType = System.Data.CommandType.StoredProcedure;
           cmd.CommandText = "dbo.dms_Report_StatusDistributors";
           cmd.CommandTimeout = 0;

           SqlDataAdapter da = new SqlDataAdapter(cmd);
           DataTable dt = new DataTable("dtDistributorsStatus");

           try
           {
               cmd.Connection.Open();
               da.Fill(dt);
               cmd.Connection.Close();
           }
           catch (Exception ex)
           {
               Credentials.writeLogFile("getDistributorsFTPStatus: " + ex.Message);
               return null;
           }
           DataSet ds = new DataSet("dsDistributorsStatus");
           ds.Tables.Add(dt);
           return ds;
       }

       public static DataSet getUnileverFTPStatus(string ambient)
       {
           string connection = Credentials.getCredentias(ambient);
           if (connection == null)
           {
               return null;
           }
           SqlCommand cmd = new SqlCommand();
           cmd.Connection = new SqlConnection(connection);
           cmd.CommandType = System.Data.CommandType.StoredProcedure;
           cmd.CommandText = "dbo.dms_Report_StatusUnilever";
           cmd.CommandTimeout = 0;

           SqlDataAdapter da = new SqlDataAdapter(cmd);
           DataTable dt = new DataTable("dtUnileverStatus");

           try
           {
               cmd.Connection.Open();
               da.Fill(dt);
               cmd.Connection.Close();
           }
           catch (Exception ex)
           {
               Credentials.writeLogFile("getUnileverFTPStatus: " + ex.Message);
               return null;
           }
           DataSet ds = new DataSet("dsUnileverStatus");
           ds.Tables.Add(dt);
           return ds;
       }
    }
}
